package com.scalar.chiptrack.sales.parser;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.commons.Address;

import java.sql.*;
import java.util.ArrayList;

/**
 * User: Sujan
 * Date: Jan 8, 2007
 * Time: 2:13:41 PM
 */
public class CustomerParserDAO
{
    private CustomerParserDAO()
    {
    }

    public static int saveCustomer(DBTransaction dbTransaction, String customerName, String customerNumber, String deliveryTerms, String customerType, String customerContactPerson, boolean isDistributor, String region, String shippingMethod, String accountNumber) throws ScalarException
    {
        Connection conn     = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int customerId;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{CALL SP_SAVE_CUSTOMER(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}" );
            cstmt.setString( 1, customerName );
            cstmt.setString( 2, customerNumber );
            cstmt.setString( 3, deliveryTerms );
            cstmt.setString ( 4, customerType );
            cstmt.setString ( 5, customerContactPerson );
            cstmt.setInt ( 6, isDistributor ? 1 : 0 );
            cstmt.registerOutParameter( 7, Types.INTEGER );
            cstmt.setString ( 8, region );
            cstmt.setString ( 9, shippingMethod );
            cstmt.setString ( 10, accountNumber );
            cstmt.execute();
            customerId = cstmt.getInt( 7 );
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    conn.commit();
                }
            }
            success = true;
        }
        catch ( SQLException e )
        {
            throw new ScalarException ( "Could not save customer", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( !success && conn != null )
                {
                    try { conn.rollback(); } catch ( SQLException ignore ){}
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); cstmt = null; } catch( SQLException ignore ){}
            }
            if ( conn != null )
            {
                try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
            }
        }
        return customerId;
    }

    public static int saveCustomerAddresses ( DBTransaction dbTransaction, int customerId, ArrayList addresses, String addressType ) throws ScalarException
    {
        Connection conn     = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int addressesSize = ( addresses == null ) ? 0 : addresses.size();
        Address address = null;
        int statusCode  = 0;
        if ( addressesSize > 0 )
        {
            try
            {
                if ( dbTransaction != null )
                {
                    conn = dbTransaction.getConnection();
                }
                else
                {
                    conn = DataSource.getConnection();
                }
                cstmt = conn.prepareCall( "{CALL SP_SAVE_CUSTOMER_ADDRESS(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ? )}" );
                for ( int i=0; i < addressesSize; i++ )
                {
                    address = ( Address )addresses.get ( i );
                    if ( addressesSize == 1 )
                    {
                        address.setDefaultAddress( "true" );
                    }
                    cstmt.setInt ( 1, customerId );
                    cstmt.setString ( 2, address.getAddress1() );
                    cstmt.setString ( 3, address.getAddress2() );
                    cstmt.setString ( 4, address.getAddress3() );
                    cstmt.setString ( 5, address.getCity() );
                    cstmt.setString ( 6, address.getState() );
                    cstmt.setString ( 7, address.getCountry() );
                    cstmt.setString ( 8, address.getZipCode() );
                    cstmt.setString ( 9, address.getContactPerson() );
                    cstmt.setString ( 10, address.getPhoneNumber() );
                    cstmt.setString ( 11, address.getFaxNumber() );
                    cstmt.setString ( 12, address.getEmail() );
                    cstmt.setString ( 13, address.getCellNumber() );
                    cstmt.setString ( 14, addressType );
                    cstmt.setString (15, address.getTagName());
                    cstmt.setInt ( 16, "true".equalsIgnoreCase ( address.getDefaultAddress() ) ? 0 : 1 );
                    cstmt.registerOutParameter ( 17, Types.INTEGER );
                    cstmt.execute();
                    statusCode = statusCode | cstmt.getInt ( 17 );
                }
                if ( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        conn.commit();
                    }
                }
                success = true;
            }
            catch ( SQLException e )
            {
                throw new ScalarException ( "Could not save customer", e );
            }
            finally
            {
                if ( dbTransaction == null )
                {
                    if ( !success )
                    {
                        try{ conn.rollback(); } catch ( SQLException ignore ){}
                    }
                }
                if ( cstmt != null )
                {
                    try { cstmt.close(); cstmt = null; } catch( SQLException ignore ){}
                }
                if ( conn != null )
                {
                    try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
                }
            }
        }
        return statusCode;
    }

    public static int getCustomerId ( DBTransaction dbTransaction, String customerNumber ) throws ScalarException {
        Connection conn     = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int customerId = 0;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT CUSTOMER_ID FROM CUSTOMER WHERE C_NUMBER = ?" );
            pstmt.setString ( 1, customerNumber );
            rset = pstmt.executeQuery();
            if ( rset.next () )
            {
                customerId = rset.getInt ( "CUSTOMER_ID" );
            }
        }
        catch ( SQLException e )
        {
            throw new ScalarException ( "Could not get CustomerId for the CustomerNumber:" + customerNumber, e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); rset = null; } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); pstmt = null; } catch(SQLException  ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); conn = null; } catch ( SQLException ignore ) {}
            }
        }
        return customerId;
    }
}
